const express = require('express');
const xss = require('xss');
const router = express.Router();
const Joi = require('joi');
const moment= require('moment');
const sqlInjectReject = require('sql-inject-reject');
const auth = require('../middleware/auth');
const admin = require('../middleware/admin');

router.get('/',sqlInjectReject(), async (req,res) => {
    const queryObj = req.query;
    const numberOfItems = 20;   // 每页可显示的数量
    let searchString = '',
        orderString = '',
        pagingString = '';
    if (queryObj.kw){           // 如果需要搜索
        searchString = `AND instr(cars.rego,'${queryObj.kw}')>0`;
    }

    if (queryObj.orderby){      // 如果需要排序
        if (queryObj.orderby == 'cmt'){
            if (queryObj.order == 'asc')
                orderString = 'ORDER BY datetime(minCmtCDT) ASC';
            else
                orderString = 'ORDER BY datetime(maxCmtCDT) DESC';
        }
        else{
            if (queryObj.orderby == 'carcdt')
                orderString = 'ORDER BY datetime(carCDT)';
            else if (queryObj.orderby == 'rate')
                orderString = 'ORDER BY rate';
            
            if (queryObj.order == 'asc')
                orderString = orderString + ' ASC';
            else
                orderString = orderString + ' DESC';
        }
    }

    if (queryObj.page){         // 如果需要翻页
        const offset = (parseInt(queryObj.page) - 1) * numberOfItems;
        pagingString = `LIMIT ${offset},${numberOfItems}`;
    }else{
        pagingString = `LIMIT 0,${numberOfItems}`;
    }
    
    let queryString = `
        SELECT cars.rego as rego, color, model, 
			datetime(cars.createDateTime) as carCDT, 
            c.userId as userId, 
            users.nickname as userName,
            max(datetime(c.createDateTime)) as maxCmtCDT,
            min(datetime(c.createDateTime)) as minCmtCDT,
            avg(c.rate) as avgRate,
            count(c.rate) as commentCount
        FROM cars
        LEFT JOIN (
	    	SELECT * FROM comments WHERE isDisabled=0
	    ) c
        ON cars.rego=c.rego
        LEFT JOIN users 
        ON users.id = c.userId
        WHERE cars.isDisabled=0 
        ${searchString}
        GROUP BY cars.rego
        ${orderString}
        ${pagingString}
    `;
    
    try {
        const db = req.app.get('db');
        const cars = await db.all(queryString);
        res.send({'cars':cars});
    } catch (err) {
        return res.status(500).send({'message':err.message});
    }
});

router.post('/',auth, async (req,res) =>{
    try{
        const {error} = validateCar(req.body);
        if(error){
            res.status(400).send({'message':error.details[0].message});
            return;
        }
        const db = req.app.get('db');
    
        const exist = await db.get('Select * from cars where rego=?',req.body.rego);
        if (!exist){
            await db.run('INSERT INTO cars (rego, color, model, createDateTime) VALUES (?,?,?,?)',
            xss(req.body.rego), // Rego：车牌号
            xss(req.body.color),
            xss(req.body.model),
            moment().format('yyyy-MM-DD HH:mm:ss'));
            const car = await db.get('Select * from cars where rego=?',req.body.rego);
            return res.send({'car':car,'message':'new car has created'});
        }else if (exist.isDisabled === 0)
            return res.status(400).send({'message':'already has this rego'});
        else if (exist.isDisabled === 1){
            await db.run('UPDATE cars SET model=?, color=?, createDateTime=?,isDisabled=? WHERE rego=?',
            xss(req.body.color),
            xss(req.body.model),
            moment().format('yyyy-MM-DD HH:mm:ss'),
            0,
            xss(req.params.rego));
            const updatedCar = await db.get('Select * from cars where rego=?',xss(req.body.rego));
            return res.status(201).send({'car':updatedCar,'message':'new car has created'});
        }
    }catch(err){
        return res.status(500).send({'message':err.message});
    }
});

router.get('/:rego', async (req,res) => {
    try{
        const db = req.app.get('db');
        const queryString = `
            SELECT cars.rego as rego, model, color, 
                cars.createDateTime as createDateTime, avg(c.rate) as avgRate, count(c.rate) as commentCount
            FROM cars
            LEFT JOIN (SELECT rego,rate FROM comments WHERE isDisabled=0) c
            ON cars.rego = c.rego
            WHERE
                cars.isDisabled = 0 AND cars.rego=?
            GROUP BY cars.rego;
        `;
        let car = await db.get(queryString,
            req.params.rego);
        if (!car) {
            return res.status(404).send({'message':'cannot find the car with the rego'});
        }
        return res.send({'car':car});
    }catch(err){
        return res.status(500).send({'message':err.message});
    }
})

router.put('/:rego',[auth,admin],async (req,res) =>{
    try{
        const db = req.app.get('db');
        const car = await isExistingCar(req,res);
        if (!car) return;
        req.body.rego = req.params.rego;
        const {error} = validateCar(req.body);
        if(error){
            res.status(400).send({'message':error.details[0].message});
            return;
        }
        
        await db.run('UPDATE cars SET model=?, color=?, createDateTime=?,isDisabled=? WHERE rego=?',
            xss(req.body.color),
            xss(req.body.model),
            moment().format('yyyy-MM-DD HH:mm:ss'),
            0,
            xss(req.params.rego));
            const updatedCar = await db.get('Select * from cars where rego=?',xss(req.body.rego));
            return res.send({'car':updatedCar,'message':'update selected car successful'});
    }catch(err){
        return res.status(500).send({'message':err.message});
    }
    
});

router.delete('/:rego',[auth,admin],async (req,res)=>{
    try{
        const db = req.app.get('db');
        const result = await isExistingCar(req,res);
        if (!result) return;
        await db.run('UPDATE cars SET isDisabled=? WHERE rego=?',1,
            req.params.rego);
        const car = await db.get('Select * from cars Where isDisabled = 1 AND rego=?;', 
            req.params.rego);
        res.send({'car':car,'message':'sccessfully deteled the car'});
    }catch(err){
        return res.status(500).send({'message':err.message});
    }
});

router.get('/:rego/comments', async (req,res) => {
    try{
        const result = await isExistingCar(req,res);
        if (!result) return;

        const queryObj = req.query;
        const numberOfItems = 20;   // 每页可显示的数量
        let rateString = '',
            pagingString = '';
        if (queryObj.rate){      // 如果需要分别显示不同评价
            if (queryObj.rate == 'p'){
                rateString = 'AND comments.rate=1';
            }else{
                rateString = 'AND comments.rate=0';
            }
        }
    
        if (queryObj.page){         // 如果需要翻页
            const offset = (parseInt(queryObj.page) - 1) * numberOfItems;
            pagingString = `LIMIT ${offset},${numberOfItems}`;
        }else{
            pagingString = `LIMIT 0,${numberOfItems}`;
        }


        const db = req.app.get('db');
        let queryString = `
            SELECT comments.id, comments.rate, comments.content,
                comments.location, 
                datetime(comments.createDateTime) as cmtCDT, 
                users.nickname as username
            FROM comments
            LEFT JOIN users
            ON comments.userId=users.id
            WHERE comments.rego=?
                AND comments.isDisabled=0
                ${rateString}
            ORDER BY datetime(comments.createDateTime) DESC
            ${pagingString}
        `;
        const comments = await db.all(queryString,
            req.params.rego);
        return res.send({'comments':comments});
    }catch(err){
        return res.status(500).send({'message':err.message});
    }
})

router.post('/:rego/comments', auth, async (req,res) => {
    try{
        const car = await isExistingCar(req,res);
        if (!car) return;
        const {error} = validateComment(req.body);
        if(error){
            res.status(400).send({'message':error.details[0].message});
            return;
        }
        const db = req.app.get('db');

        const result = await db.run('INSERT INTO comments (userId,rego,content,rate,location,createDateTime) VALUES (?,?,?,?,?,?)',
            xss(req.user.id),
            xss(req.params.rego),
            xss(req.body.content), 
            parseInt(req.body.rate),
            xss(req.body.location),
            moment().format('yyyy-MM-DD HH:mm:ss'));
        const comment = await db.get('Select * from comments where id=?',result.lastID);
        return res.status(201).send({'comment':comment,'message':'new comment has posted'});
    }catch(err){
        return res.status(500).send({'message':err.message});
    }
})

function validateCar(car){
    const schema = Joi.object({
        rego : Joi.string().min(7).max(8).required(),
        color: Joi.string().min(1).max(30).required(),
        model: Joi.string().min(1).max(30).required(),
    });

    return schema.validate(car);
}

async function isExistingCar(req,res){
    const db = req.app.get('db');
    let car = await db.get('Select * from cars Where isDisabled = 0 AND rego=?;',
        req.params.rego);
    if (!car) {
        res.status(404).send({'message':'cannot find the car with the rego'});
        return false;
    }
    return car;
}

function validateComment(comment){
    const schema = Joi.object({
        content: Joi.string().min(3).max(50).required(),
        rate: Joi.number().integer().min(0).max(1).required(),
        location: Joi.string().min(3).max(50).required()
    });

    return schema.validate(comment);
}

module.exports = router;